import random
import pymysql
import requests
from bs4 import BeautifulSoup
# 导入lxml的etree模块
from lxml import etree

headers={
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36"
}
url = 'http://www.techlabplt.com:8080/BD-PC/proxy.html'

response = requests.get(url=url, headers=headers)
response.encoding='utf-8'
html = etree.HTML(response.text)

conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', port=3306,
database='spider')

pageHtml = BeautifulSoup(response.text, "html.parser")
# 获取html内的div，div的class为proxy-title。里面包含着此网站的标题数据
title = pageHtml.find("div", attrs={"class": "proxy-title"}).text.replace(" ", '').replace("\n", '')
# 输出标题
print(title)

# 代理网站的tr列表
proxy_list = html.xpath('/html/body/div[3]/div[2]/div/div[2]/table/tbody/tr')
for proxy in proxy_list:
   # ip 地址
   ip = proxy[0].text
   # 端口号
   port = proxy[1].text
   # 代理类型
   type = proxy[3].text
   # 地理位置
   position = proxy[5].text
   values = (
      ip,
      port,
      type,
      position
   )
   # 定义插入语句
   result = "insert into proxy(ip,port,type,position) values('%s','%s','%s','%s')"%values
   try:
      cursor = conn.cursor()
      cursor.execute(result)
   except:
      # 发生异常，回滚
      print("发生异常")
      conn.rollback()

# 提交操作
conn.commit()
# 关闭连接
cursor.close()
conn.close()